package com.lubase.metadata.mapper;

import com.lubase.metadata.model.ColumnMetaModel;
import com.lubase.metadata.model.ConstraintMetaModel;
import com.lubase.metadata.model.TableMetaModel;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * 多数据库测试mapper
 */
@Mapper
public interface OracleMetadataReadMapper {

    @Select("SELECT OWNER||'.'||TABLE_NAME as table_name ,COMMENTS as table_comment \n" +
            "   from all_tab_comments where OWNER=#{databaseCode} and TABLE_NAME not like 'BIN%'")
    @Results({
            @Result(property = "tableName", column = "table_name"),
            @Result(property = "tableComment", column = "table_comment")
    })
    List<TableMetaModel> getTableList(String databaseCode);


    @Select(" SELECT c.OWNER||'.'||c.TABLE_NAME as table_name,c.DATA_TYPE||'('||c.DATA_LENGTH||')'  as column_type,m.COLUMN_NAME,m.COMMENTS as column_comment " +
            "from all_tab_columns c LEFT JOIN all_col_comments m on c.TABLE_NAME=m.TABLE_NAME and c.COLUMN_NAME=m.COLUMN_NAME" +
            " where c.OWNER=#{databaseCode}")
    @Results({
            @Result(property = "tableName", column = "table_name"),
            @Result(property = "columnName", column = "column_name"),
            @Result(property = "columnComment", column = "column_comment"),
            @Result(property = "columnType", column = "column_type")
    })
    List<ColumnMetaModel> getTableColumnListByDatabase(@Param("databaseCode") String databaseCode);

    @Select("SELECT \n" +
            "    a.OWNER||'.'||a.table_name AS table_name,\n" +
            "    a.constraint_name AS constraint_name,\n" +
            "    b.column_name AS column_name,\n" +
            "    c.OWNER||'.'||c.table_name AS referenced_table_name,\n" +
            "     d.column_name AS referenced_column_name\n" +
            "FROM \n" +
            "    all_constraints a\n" +
            "JOIN \n" +
            "    all_cons_columns b ON a.constraint_name = b.constraint_name \n" +
            "    AND a.owner = b.owner\n" +
            "JOIN \n" +
            "    all_constraints c ON a.r_owner = c.owner \n" +
            "    AND a.r_constraint_name = c.constraint_name\n" +
            " JOIN \n" +
            "    all_cons_columns d ON c.constraint_name = d.constraint_name \n" +
            "    AND c.owner = d.owner \n" +
            "WHERE \n" +
            "    a.constraint_type = 'R' \n" +
            "    AND a.owner = #{databaseCode}")
    @Results({
            @Result(property = "constraintName", column = "constraint_name"),
            @Result(property = "tableName", column = "table_name"),
            @Result(property = "columnName", column = "column_name"),
            @Result(property = "referencedTableName", column = "referenced_table_name"),
            @Result(property = "referencedColumnName", column = "referenced_column_name")
    })
    List<ConstraintMetaModel> getTableConstraintList(@Param("databaseCode") String databaseCode);
}
